﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System;

namespace ConsolidationTool.Common
{
    class OpenXMLExcelCommon
    {
        public static bool InsertStringIntoCell(string fileName, string sheetName, string addressName, string value)
        {
            // Given a file, a sheet, and a cell, insert a specified string.
            // For example: InsertStringIntoCell("C:\Test.xlsx", "Sheet3", "C3", "Microsoft");
            // If the string exists in the shared string table, get its index.
            // If the string doesn't exist in the shared string table, add it and get the next index.
            // Then, the remainder is the same as inserting a number, but insert the string index instead
            // of a value. Also, set the cell's t attribute to be the value "s".
            // Assume failure.
            bool returnValue = false;

            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();

                if (theSheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(theSheet.Id))).Worksheet;
                    Cell theCell = InsertCellInWorksheet(ws, addressName);

                    // Either retrieve the index of an existing string,
                    // or insert the string into the shared string table
                    // and get the index of the new item.
                    int stringIndex = InsertSharedStringItem(wbPart, value);

                    theCell.CellValue = new CellValue(stringIndex.ToString());
                    theCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                    // Save the worksheet.
                    ws.Save();
                    returnValue = true;
                }
            }
            return returnValue;
        }

        public static bool InsertNumberIntoCell(string fileName, string sheetName, string addressName, int value)
        {

            // Given a file, a sheet, and a cell, insert a specified value.
            // For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)
            // Assume failure.
            bool returnValue = false;

            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
                  Where(s => s.Name == sheetName).FirstOrDefault();
                if (theSheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(theSheet.Id))).Worksheet;
                    Cell theCell = InsertCellInWorksheet(ws, addressName);

                    // Set the value of cell A1.
                    theCell.CellValue = new CellValue(value.ToString());
                    theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                    
                    // Save the worksheet.
                    ws.Save();
                    returnValue = true;
                }
            }

            return returnValue;
        }

        // Given the main workbook part, and a text value, insert the text into the shared
        // string table. Create the table if necessary. If the value already exists, return
        // its index. If it doesn't exist, insert it and return its new index.
        private static int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            // Insert a value into the shared string table, creating the table if necessary.
            // Insert the string if it's not already there.
            // Return the index of the string.

            int index = 0;
            bool found = false;
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            if (stringTablePart == null)
            {
                // Create it.
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
            }

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new Text(value)));
                stringTable.Save();
            }

            return index;
        }

        // Given a Worksheet and an address (like "AZ254"), either return a cell reference, or 
        // create the cell reference and return it.
        private static Cell InsertCellInWorksheet(Worksheet ws, string addressName)
        {
            // Use regular expressions to get the row number and column name.
            // If the parameter wasn't well formed, this code
            // will fail:

            uint rowNumber = Convert.ToUInt32(GetNumber(addressName));
            string colName = GetLetter(addressName);

            SheetData sheetData = ws.GetFirstChild<SheetData>();
            string cellReference = (colName + rowNumber.ToString());
            Cell theCell = null;

            // If the worksheet does not contain a row with the specified row index, insert one.
            var theRow = sheetData.Elements<Row>().
              Where(r => r.RowIndex.Value == rowNumber).FirstOrDefault();
            if (theRow == null)
            {
                theRow = new Row();
                theRow.RowIndex = rowNumber;
                sheetData.Append(theRow);
            }

            // If the cell you need already exists, return it.
            // If there is not a cell with the specified column name, insert one.  
            Cell refCell = theRow.Elements<Cell>().
              Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
            if (refCell != null)
            {
                theCell = refCell;
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                foreach (Cell cell in theRow.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                theCell = new Cell();
                theCell.CellReference = cellReference;

                theRow.InsertBefore(theCell, refCell);
            }
            return theCell;
        }


        public static string GetLetter(string info)
        {
            Regex rxNumber = new Regex("[0-9]*[1-9][0-9]*$");
            string colName = rxNumber.Replace(info, "");
            return colName;
        }

        public static int GetNumber(string info)
        {
            Regex rxNumber = new Regex("[0-9]*[1-9][0-9]*$");
            Regex rxLetter = new Regex("[A-Za-z]+$");

            string colName = rxNumber.Replace(info, "");

            int rowNumber = Convert.ToInt32(info.Substring(info.IndexOf(colName) + 1));
            return rowNumber;
        }

        public static bool InsertRow(SheetData sheetData, WorkbookPart wbPart, Worksheet ws, uint rowIndex)
        {

            bool returnValue = false;

            string cellReference = string.Empty;

            var theRow = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
            if (theRow == null)
            {
                theRow = new Row();
                theRow.RowIndex.Value = rowIndex;
                sheetData.Append(theRow);
            }

            var newRow = (Row)theRow.CloneNode(false);

            uint newRowIndex;
            IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= theRow.RowIndex);
            foreach (Row row in rows)
            {
                newRowIndex = row.RowIndex.Value + 1;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    cellReference = cell.CellReference.Value;
                    cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                }
                row.RowIndex = new UInt32Value(newRowIndex);
            }

            sheetData.InsertBefore(newRow, theRow);

            ws.Save();

            returnValue = true;

            return returnValue;
        }




        public static bool InsertStringDictionaryIntoRow(SheetData sheetData, WorkbookPart wbPart, Worksheet ws, uint rowIndex, StringDictionary dic)
        {

            bool returnValue = false;

            string cellReference = string.Empty;

            var theRow = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
            if (theRow == null)
            {
                theRow = new Row();
                theRow.RowIndex.Value = rowIndex;
                sheetData.Append(theRow);
            }

            var newRow = (Row)theRow.CloneNode(false);

            uint newRowIndex;
            IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= theRow.RowIndex);
            foreach (Row row in rows)
            {
                newRowIndex = row.RowIndex.Value + 1;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    cellReference = cell.CellReference.Value;
                    cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                }
                row.RowIndex = new UInt32Value(newRowIndex);
            }

            sheetData.InsertBefore(newRow, theRow);


            ws.Save();


            foreach (string key in dic.Keys)
            {
                {
                    InsertStringIntoCell(wbPart, ws, key + rowIndex, dic[key]);
                }

            }

            returnValue = true;

            return returnValue;
        }

        public static bool InsertStringDictionaryIntoRow(string fileName, string sheetName, uint rowIndex, StringDictionary dic)
        {

            bool returnValue = false;

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();

                if (theSheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(theSheet.Id))).Worksheet;
                    SheetData sheetData = ws.GetFirstChild<SheetData>();

                    string cellReference = string.Empty;

                    var theRow = sheetData.Elements<Row>().Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                    if (theRow == null)
                    {
                        theRow = new Row();
                        theRow.RowIndex.Value = rowIndex;
                        sheetData.Append(theRow);
                    }

                    var newRow = (Row)theRow.Clone();

                    uint newRowIndex;
                    IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= theRow.RowIndex);
                    foreach (Row row in rows)
                    {
                        newRowIndex = row.RowIndex.Value + 1;
                        foreach (Cell cell in row.Elements<Cell>())
                        {
                            cellReference = cell.CellReference.Value;
                            cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                        }
                        row.RowIndex = new UInt32Value(newRowIndex);
                    }

                    sheetData.InsertBefore(newRow, theRow);


                    ws.Save();


                    foreach (string key in dic.Keys)
                    {
                        InsertStringIntoCell(wbPart, ws, key + rowIndex, dic[key]);
                    }


                    returnValue = true;
                }
            }
            return returnValue;
        }


        public static bool InsertStringCollectionIntoRow(string fileName, string sheetName, string beginAddressName, StringCollection values)
        {
            bool returnValue = false;

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;

                Regex rxNumber = new Regex("[0-9]*[1-9][0-9]*$");
                Regex rxLetter = new Regex("[A-Za-z]+$");

                string colName = rxNumber.Replace(beginAddressName, "");

                uint rowNumber = uint.Parse(beginAddressName.Substring(beginAddressName.IndexOf(colName) + 1));



                Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();

                if (theSheet != null)
                {
                    Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(theSheet.Id))).Worksheet;
                    SheetData sheetData = ws.GetFirstChild<SheetData>();

                    string cellReference = (colName + (rowNumber - 1).ToString());

                    var theRow = sheetData.Elements<Row>().
                      Where(r => r.RowIndex.Value == rowNumber).FirstOrDefault();
                    if (theRow == null)
                    {
                        theRow = new Row();
                        theRow.RowIndex = rowNumber;
                        sheetData.Append(theRow);
                    }


                    var newRow = (Row)theRow.CloneNode(false);
                    uint newRowIndex;
                    IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= theRow.RowIndex);
                    foreach (Row row in rows)
                    {
                        newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);
                        foreach (Cell cell in row.Elements<Cell>())
                        {
                            cellReference = cell.CellReference.Value;
                            cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                        }
                        row.RowIndex = new UInt32Value(newRowIndex);
                    }

                    sheetData.InsertBefore(newRow, theRow);

                    ws.Save();
                    returnValue = true;
                }
            }
            return returnValue;
        }



        private static Cell CreateTextCell(string column, string text, uint index)
        {
            Cell cell = new Cell();
            cell.DataType = CellValues.String;
            cell.CellReference = column + index;
            cell.CellValue = new CellValue(text);
            return cell;
        }



        private static Row CreateRow(Row refRow, SheetData sheetData, Row refRow1)
        {
            uint newRowIndex = 0;
            var newRow = new Row() { RowIndex = refRow.RowIndex.Value };

            // Loop through all the rows in the worksheet with higher row
            // index values than the one you just added. For each one,
            // increment the existing row index.
            IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRow.RowIndex.Value);
            foreach (Row row in rows)
            {
                newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);

                foreach (Cell cell in row.Elements<Cell>())
                {
                    // Update the references for reserved cells.
                    string cellReference = cell.CellReference.Value;
                    cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                }
                // Update the row index.
                row.RowIndex = new UInt32Value(newRowIndex);
            }

            sheetData.InsertBefore(newRow, refRow);

            return newRow;
        }



        public static bool InsertCaculateValueIntoCell(WorkbookPart wbPart, Worksheet ws, string addressName, string caculateColumnName, int caculateBegionRowIndex)
        {
            bool returnValue = false;
            Cell theCell = InsertCellInWorksheet(ws, addressName);

            string firstRow = caculateColumnName + caculateBegionRowIndex.ToString();

            string lastRow = caculateColumnName + (caculateBegionRowIndex + 30).ToString();
            string formula = "=SUM(" + firstRow + " : " + lastRow + ")";

            theCell.CellFormula = new CellFormula { CalculateCell = true, Text = formula };
            theCell.DataType = CellValues.Number;

            ws.Save();
            returnValue = true;
            return returnValue;
        }


        public static bool InsertStringIntoCell(WorkbookPart wbPart, Worksheet ws, string addressName, string value)
        {
            bool returnValue = false;
            Cell theCell = InsertCellInWorksheet(ws, addressName);
            int stringIndex = InsertSharedStringItem(wbPart, value);
            theCell.CellValue = new CellValue(stringIndex.ToString());
            //theCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
            //theCell.StyleIndex = 1;
          //  ws.Save();
            returnValue = true;
            return returnValue;
        }


        public static bool InsertStringIntoCell(WorkbookPart wbPart, Worksheet ws, string addressName, object value)
        {
            bool returnValue = false;
            Cell theCell = InsertCellInWorksheet(ws, addressName);

            if (value.GetType() == typeof(double))
            {
                //theCell.StyleIndex = UInt32Value.FromUInt32(5);
                theCell.CellValue = new CellValue(value.ToString());
                //theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                //theCell.StyleIndex = UInt32Value.FromUInt32(8);
                // theCell.CellValue = new CellValue(value.ToString());
            }
            else if (value.GetType() == typeof(DateTime))
            {
                theCell.CellValue = new CellValue(value.ToString());
                //theCell.CellValue = new CellValue { Text = Convert.ToDateTime(value).ToOADate().ToString() };
            }
            else if (value.GetType() == typeof(decimal))
            {
                //theCell.StyleIndex = UInt32Value.FromUInt32(5);
                theCell.CellValue = new CellValue(value.ToString());
                //theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                //theCell.StyleIndex = UInt32Value.FromUInt32(8);
                // theCell.CellValue = new CellValue(value.ToString());

            }

           // ws.Save();
            returnValue = true;
            return returnValue;
        }

        public static bool InsertStringIntoCell(WorkbookPart wbPart, Worksheet ws, string addressName, int value)
        {
            bool returnValue = false;
            Cell theCell = InsertCellInWorksheet(ws, addressName);

            theCell.StyleIndex = UInt32Value.FromUInt32(5);
            theCell.CellValue = new CellValue(value.ToString());
            theCell.DataType = new EnumValue<CellValues>(CellValues.Number);


            ws.Save();
            returnValue = true;
            return returnValue;
        }


        public static string GetCellValue(WorkbookPart wbPart, WorksheetPart wsPart, string addressName)
        {
            Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
            return GetCellValue(wbPart, theCell);
        }

        public static string GetCellValue(WorkbookPart wbPart, Row theRow, string columnName)
        {
            string cellReference = (columnName + theRow.RowIndex.Value.ToString());
            Cell theCell = theRow.Elements<Cell>().
              Where(c => c.CellReference.Value == cellReference).FirstOrDefault();
            return GetCellValue(wbPart, theCell);
        }

        public static string GetCellValue(WorkbookPart wbPart, Cell theCell)
        {
            string value = null;
            if (theCell != null)
            {
                value = theCell.InnerText;
                if (theCell.DataType != null)
                {
                    switch (theCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }
                            break;
                        case CellValues.Number:
                            break;
                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                default:
                                    value = "TRUE";
                                    break;
                            }
                            break;
                    }
                }
            }
            return value;
        }


        public static Row GetRowInWorksheet(Worksheet ws, uint rowNumber)
        {
            SheetData sheetData = ws.GetFirstChild<SheetData>();
            var theRow = sheetData.Elements<Row>().
              Where(r => r.RowIndex.Value == rowNumber).FirstOrDefault();
            return theRow;
        }

        public static Row GetRowInWorksheet(SheetData sheetData, uint rowNumber)
        {
            var theRow = sheetData.Elements<Row>().
              Where(r => r.RowIndex.Value == rowNumber).FirstOrDefault();
            return theRow;
        }
    }
}
